import sys, os
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), os.path.pardir)))


from datetime import datetime
import pandas as pd
from sqlalchemy import create_engine
from AppStoreConnect import Subscription, AppProduct

def get_subscription_event_df(start_date:datetime, end_date:datetime, subscription_apple_id:str) -> pd.DataFrame:
    # 创建AppDB实例
    engine = create_engine('mysql+pymysql://appconnect:pobxoj-Mopsy5-bycqac@47.116.122.58:3306/app')

    sql = f'select * from subscription_event where event_date between "{start_date.strftime('%Y-%m-%d')}" and "{end_date.strftime('%Y-%m-%d')}" and subscription_apple_id="{subscription_apple_id}"'
    print(sql)
    # 获取从start_date到end_date的 订阅事件数据
    df = pd.read_sql(sql, engine)

    activation_events = Subscription.activation_events.values()

    # 按国家统计event in activaion_events数据，quantity之和，不要index
    activation_df = df[df['event'].isin(activation_events)].groupby('country').agg({'quantity': 'sum'})

    # 按国家统计event in cancelation_events数据，quantity之和，不要index
    cancelation_df = df[df['event'].isin(Subscription.cancelation_events.values())].groupby('country').agg({'quantity': 'sum'})

    # 按国家统计event in conversion_to_standard_price_events数据，quantity之和，不要index
    conversion_to_standard_price_df = df[df['event'].isin(Subscription.conversion_to_standard_price_events.values())].groupby('country').agg({'quantity': 'sum'})  


    # 按国家合并三个DataFrame
    result_df = activation_df.merge(cancelation_df, on='country', suffixes=('_activation', '_cancelation'))
    result_df = result_df.merge(conversion_to_standard_price_df, on='country')
    
    result_df.columns = ['activation', 'cancelation', 'conversion_to_standard_price']
    
    return result_df


if __name__ == "__main__":
    start_date = datetime(2024, 2, 1)
    end_date = datetime(2024, 2, 29)
    df = get_subscription_event_df(start_date=start_date, end_date=end_date, subscription_apple_id=AppProduct.Sub_Hearty_1_Year)
    print(df.head(10))